set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
insert  overwrite  table jms_dm.dm_network_entry_port_aging_sign_detail_dt partition (dt)
-- 网点进港时效运单明细
select
        billcode   --运单
       ,end_agent_code  --末端代理区code
       ,end_agent_name  -- 末端代理区
       ,end_center_code  --末端中心code
       ,end_center_name  --末端中心
       ,end_franchisee_code  --末端加盟商code
       ,end_franchisee_name  --末端加盟商
       ,final_sign_network_code  -- 末端网点
       ,final_sign_network_name  --末端网点
       ,deliver_shift     --班次
     ,goods_type_code           --货物类型code
     ,goods_type_name           -- 货物类型
     ,order_source_code         --订单来源code
     ,order_source_name         --订单来源
     ,deliver_township_id  --乡镇id
     ,deliver_township_name  --乡镇
     ,case when (end_network_source = '共配' or deliver_source = '共配' or sign_source = '共配') then 1 else 2 end is_common--是否共配
     ,planned_departure_time                --规划接件时间
     ,actual_departure_time               --实际接件时间
     ,if(substr(actual_departure_time,12,8) is not null and (planned_departure_time is null or substr(actual_departure_time, 12, 8) <= planned_departure_time), 1, 0)   as is_departure      -- 接件是否准点
     ,case when actual_departure_time is null and if(substr(actual_departure_time,12,8) is not null and (planned_departure_time is null or substr(actual_departure_time,12,8) <= planned_departure_time), 1, 0) =0 then 0 --漏扫
           when actual_departure_time is not null and planned_departure_time is not null and  substr(actual_departure_time,12,8) > planned_departure_time  then 1
           else 3 end   as is_departure_type --              --不准点类型
     , planned_arrival_time               --规划网点到车时间
     , actual_arrival_time                   --网点到车时间
     , if(substr(actual_arrival_time,12,8) is not null and (planned_arrival_time is null or substr(actual_arrival_time,12,8) <= planned_arrival_time),1,0) as is_arrival        --到车是否准点
     , case when actual_arrival_time is null and if(substr(actual_arrival_time,12,8) is not null and  (planned_arrival_time is null or substr(actual_arrival_time,12,8) <= planned_arrival_time),1,0)=0  then 0 --漏扫
           when actual_arrival_time is not null and planned_arrival_time is not null and substr(actual_arrival_time,12,8) > planned_arrival_time then 1
           else 3 end   as is_arrival_type   --              --不准点类型           --不准点类型
         ,warehouse_end_time             --派仓结束时间
         ,deliver_time        --出仓扫描时间
     ,if(substr(deliver_time,12,8) is not null and (warehouse_end_time is null or substr(deliver_time,12,8) <= warehouse_end_time), 1,0) as is_deliver        --出仓是否准点
         ,case when  deliver_time is null and if(substr(deliver_time,12,8) is not null and (warehouse_end_time is null or substr(deliver_time,12,8) <= warehouse_end_time), 1,0)=0 then 0
           when deliver_time is not  null and warehouse_end_time is not null and substr(deliver_time,12,8) > warehouse_end_time then 1
           else 3 end as is_deliver_type   --不准点类型
        ,dt as date_time    -- 中心法发件日期
     ,to_date(final_plan_sign_time)as final_plan_sign_date
     ,final_plan_sign_time
     ,aging_sign_time
     ,if(aging_sign_time is not null and aging_sign_time<=final_plan_sign_time,1,0) as is_aging_sign_on_time
     ,shipment_no --任务号
     -- 以下为新加字段
    ,case when  aging_sign_time is null then 0
           when aging_sign_time >final_plan_sign_time then 1
           else 6 end as is_aging_sign_type   --签收类型  0未签 1延误 6准点
      ,actual_sign_time  --实际签收时间
     ,case when  actual_sign_time is null then 0
           when actual_sign_time > final_plan_sign_time then 1
           else 6 end as is_active_sign_type   --签收类型  0未签 1延误 6准点
     ,terminal_type   --对接终端类型 驿站or柜机
    ,if(actual_sign_time is not null and actual_sign_time<=final_plan_sign_time,1,0) as is_active_sign_on_time--实际签收是否准点
     ,deadline_signing_time  --截止签收时间
     ,difficult_type
     , dt        -- 中心法发件日期
from jms_dwd.dwd_terminal_sign_all_detail_new_dt
where dt >= date_sub('{{ execution_date | cst_ds }}',14)
  and dt <= '{{ execution_date | cst_ds }}'
  and is_reject_reback_transfer = 2
  and is_end_piece = 2
  and end_center_send_time <= nvl(actual_sign_time,'9999-99-99')
distribute by dt,pmod(hash(rand()),100);
